"""
Extractor Database Models
Read-only models for accessing telemetry metadata from extractor database
"""

from datetime import datetime
from typing import Optional, List
from sqlalchemy import (
    Column, String, Integer, Text, Boolean, ForeignKey, DateTime, Index
)
from sqlalchemy.orm import relationship

from app.core.database import Base

# Import Directory from business to establish relationship
# This import is at the bottom to avoid circular imports


class Metric(Base):
    """Core metric model for UMA histograms, enums, and UKM events/metrics"""
    __tablename__ = "metrics"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False, index=True)  # Part of composite unique key
    type = Column(String, nullable=False)  # histogram, enum, ukm_event, ukm_metric
    summary = Column(Text)  # Database has 'summary' instead of 'description'
    units = Column(String)
    expires_after = Column(String)
    component = Column(String, index=True)  # Added index
    singular = Column(Boolean, default=False)  # Added missing field
    file_path = Column(String, nullable=False)  # Kept for backward compatibility
    directory_id = Column(Integer, ForeignKey("directories.id"), nullable=False, index=True)  # New foreign key
    filename = Column(String, nullable=False)  # New: extracted filename
    team_id = Column(String, ForeignKey("teams.id"), nullable=True, index=True)  # Team assignment foreign key
    line_number = Column(Integer)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow)  # Added missing field
    analysis_status = Column(String, default='NOT_ANALYZED')  # Analysis tracking
    analysis_notes = Column(Text)  # Analysis notes
    analyzed_by = Column(String)  # Who analyzed this metric
    analyzed_at = Column(DateTime)  # When analysis was performed
    # Chinese translation fields
    name_zh = Column(Text)  # Chinese name
    summary_zh = Column(Text)  # Chinese summary
    units_zh = Column(String)  # Chinese units
    component_zh = Column(String)  # Chinese component

    # Relationships
    owners = relationship("MetricOwner", back_populates="metric")
    # Note: directory relationship handled in DirectoryTreeService to avoid circular imports
    # team relationship will be added through lazy loading to avoid circular import
    # histogram_details relationship removed due to foreign key ambiguity - will be accessed directly
    # enum_values relationship removed due to field name mismatch (enum_id vs metric_id)
    histogram_tokens = relationship("HistogramToken", back_populates="metric")

    # Add composite unique constraint
    __table_args__ = (
        Index('idx_metrics_name_type', 'name', 'type', unique=True),
    )


class MetricOwner(Base):
    """Ownership information for metrics"""
    __tablename__ = "metric_owners"

    id = Column(Integer, primary_key=True, autoincrement=True)
    metric_id = Column(Integer, ForeignKey("metrics.id"), nullable=False, index=True)  # Added index
    owner_email = Column(String, nullable=False)  # Database has 'owner_email' instead of 'owner'
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    metric = relationship("Metric", back_populates="owners")


class HistogramDetail(Base):
    """Detailed configuration for UMA histograms"""
    __tablename__ = "histogram_details"

    id = Column(Integer, primary_key=True, autoincrement=True)
    metric_id = Column(Integer, ForeignKey("metrics.id"), nullable=False, unique=True, index=True)
    enum_name = Column(String)  # Name of associated enum
    enum_id = Column(Integer, ForeignKey("metrics.id"))  # ID of associated enum (for performance)
    improvement_direction = Column(String)  # HIGHER_IS_BETTER, LOWER_IS_BETTER, NEITHER_IS_BETTER
    deprecated = Column(Boolean, default=False)  # Whether histogram is deprecated
    obsolete_message = Column(Text)  # Message explaining deprecation

    # Relationships
    metric = relationship("Metric", foreign_keys="HistogramDetail.metric_id")
    enum_metric = relationship("Metric", foreign_keys="HistogramDetail.enum_id")


class EnumValue(Base):
    """Value definitions for enum metrics"""
    __tablename__ = "enum_values"

    id = Column(Integer, primary_key=True, autoincrement=True)
    enum_id = Column(Integer, ForeignKey("metrics.id"), nullable=False, index=True)  # Database has 'enum_id' not 'metric_id'
    value = Column(Integer, nullable=False)  # Database has INTEGER not STRING
    label = Column(String, nullable=False)  # Database has 'label' not 'description'
    summary = Column(Text)  # Database has 'summary' not 'description'
    # Chinese translation fields
    label_zh = Column(String)  # Chinese label
    summary_zh = Column(Text)  # Chinese summary

    # Relationships - will need to be updated since this references metrics as enum_id
    # metric = relationship("Metric", back_populates="enum_values")


class UkmMetric(Base):
    """UKM specific metrics information"""
    __tablename__ = "ukm_metrics"

    id = Column(Integer, primary_key=True, autoincrement=True)
    event_id = Column(Integer, ForeignKey("metrics.id"), nullable=False, index=True)
    metric_name = Column(String, nullable=False)
    summary = Column(Text)
    deprecated = Column(Boolean, default=False)
    obsolete_message = Column(Text)


class Token(Base):
    """Tokens for dynamic histogram name generation"""
    __tablename__ = "tokens"

    id = Column(Integer, primary_key=True, autoincrement=True)
    token_name = Column(String, nullable=False, unique=True)  # Database has 'token_name' not 'name'

    # Relationships
    variants = relationship("Variant", back_populates="token")


class Variant(Base):
    """Variants for tokens (multiple possible values)"""
    __tablename__ = "variants"  # Database has 'variants' not 'token_variants'

    id = Column(Integer, primary_key=True, autoincrement=True)
    token_id = Column(Integer, ForeignKey("tokens.id"), nullable=False)
    variant_name = Column(String, nullable=False)  # Database has 'variant_name' not 'name'
    variant_summary = Column(Text)  # Database has 'variant_summary' not 'description'
    # Chinese translation fields
    variant_name_zh = Column(String)  # Chinese variant name
    variant_summary_zh = Column(Text)  # Chinese variant summary

    # Relationships
    token = relationship("Token", back_populates="variants")


class HistogramToken(Base):
    """Token associations for histogram metrics"""
    __tablename__ = "histogram_tokens"

    id = Column(Integer, primary_key=True, autoincrement=True)
    metric_id = Column(Integer, ForeignKey("metrics.id"), nullable=False)
    token_key = Column(String, nullable=False)
    token_name = Column(String, nullable=False)

    # Relationships
    metric = relationship("Metric", back_populates="histogram_tokens")


# Utility Views and Complex Queries
class TelemetryMetricView:
    """Composite view for unified metric queries across different types"""

    def __init__(
        self,
        id: int,
        name: str,
        type: str,
        description: Optional[str],
        units: Optional[str],
        component: Optional[str],
        file_path: str,
        line_number: Optional[int],
        owner: Optional[str],
        metadata: dict
    ):
        self.id = id
        self.name = name
        self.type = type
        self.description = description
        self.units = units
        self.component = component
        self.file_path = file_path
        self.line_number = line_number
        self.owner = owner
        self.metadata = metadata


class TeamAssignmentResult:
    """Result of team assignment based on file path matching"""

    def __init__(
        self,
        metric_id: int,
        metric_name: str,
        file_path: str,
        assigned_team_id: Optional[str],
        assigned_team_name: Optional[str],
        assignment_pattern: Optional[str]
    ):
        self.metric_id = metric_id
        self.metric_name = metric_name
        self.file_path = file_path
        self.assigned_team_id = assigned_team_id
        self.assigned_team_name = assigned_team_name
        self.assignment_pattern = assignment_pattern